﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using DotNetCommon;
using DotNetCommon.Extensions;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using DotNetCommon.Data;
using Microsoft.SqlServer.Types;
using System.Threading.Tasks;
using System.Collections.Concurrent;
using System.Threading;
using DotNetCommon.Logger;
using DBUtil.SqlSegment;
using DBUtil.Provider.SqlServer.SqlSegment;
using System.Reflection;

namespace DBUtil.Provider.SqlServer
{
    /// <summary>
    /// Microsoft SQL Server操作对象
    /// </summary>
    public partial class SqlServerAccess : DBAccess
    {
        private static ILogger<SqlServerAccess> logger = LoggerFactory.CreateLogger<SqlServerAccess>();

        protected override DbConnection GetConnectionByConnectString() => new SqlConnection(DBConn);

        #region 表名或标识符分析/解析
        /// <summary>
        /// 标识符引用标记: [],""
        /// </summary>
        public override List<string> QuoteIdentifierCharacters
        {
            get => new List<string>() { "[]", "\"\"" };
        }
        #endregion

        #region 创建DataAdapter
        /// <summary>
        /// 创建DataAdapter
        /// </summary>
        /// <returns></returns>
        protected override DataAdapter CreateAdapter(DbCommand cmd)
        {
            return new SqlDataAdapter(cmd as SqlCommand);
        }
        #endregion

        #region 创建参数 CreatePara
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <returns></returns>
        public override DbParameter CreatePara()
        {
            return new SqlParameter();
        }
        #endregion

        #region 初始化
        private static Lazy<string> appName = new Lazy<string>(() =>
        {
            var name = $"DBUtil-{Assembly.GetEntryAssembly().GetName().Name}";
            if (name.Length > 100) name = name.Substring(0, 100);
            return name;
        });
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="DBConn">连接字符串</param>
        /// <param name="Settings">设置</param>
        public SqlServerAccess(string DBConn, DBSetting Settings) : base(DBType.SQLSERVER, resetConnectionString(DBConn), "@", false, Settings)
        {
        }

        private static string resetConnectionString(string connectionString)
        {
            var builder = new SqlConnectionStringBuilder();
            var defaultAppname = builder.ApplicationName;
            builder.ConnectionString = connectionString;
            if (defaultAppname == builder.ApplicationName)
            {
                builder.ApplicationName = appName.Value;
            }
            return builder.ConnectionString;
        }
        #endregion

        #region 获取当前db/schema/user的sqlseg
        public override string GetCurrentSchemaSqlSeg() => "SCHEMA_NAME()";
        public override string GetCurrentDataBaseSqlSeg() => "DB_NAME()";
        public override string GetCurrentUserSqlSeg() => "CURRENT_USER";
        public override string GetCurrentLoginUserSqlSeg() => "SYSTEM_USER";
        public override string GetCurrentDataBaseVersionSqlSeg() => "@@VERSION";
        #endregion

        #region 将任意对象转为可拼接的sql
        /// <summary>
        /// 将任意对象转为可拼接的sql
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="args">参数,如: 
        /// <list type="number">
        /// <item>guid可使用 "N/D/B/P/X"</item>
        /// <item>datetime可使用 "yyyy-MM-dd HH:mm:ss.fff"</item>
        /// </list>
        /// </param>
        /// <returns></returns>
        public override Result<string> ConvertToSqlSegment(object obj)
        {
            //sqlserver 使用 N''形式
            if (obj is string str) return Result.Ok($"N'{DealSqlInject(str)}'");
            var res = base.ConvertToSqlSegment(obj);
            if (res.Success) return res;

            //地理 几何
            if (obj is SqlGeography geography)
            {
                var grid = geography.STSrid;
                return Result.Ok($"geography::STGeomFromText('{geography}', {grid})");
            }
            if (obj is SqlGeometry geometry)
            {
                var grid = geometry.STSrid;
                return Result.Ok($"geometry::STGeomFromText('{geometry}', {grid})");
            }
            //层次结构
            if (obj is SqlHierarchyId hierarchyId)
            {
                return Result.Ok($"'{hierarchyId}'");
            }

            return Result.NotOk($"未能转换 {obj.GetType().FullName} 类型数据到sql!");
        }
        #endregion

        #region GetSqlForPageSize
        /// <summary>获得分页的查询语句
        /// </summary>
        /// <param name="selectSql">查询sql如:select id,name from person where age>10</param>
        /// <param name="strOrder">排序字句如:order by id</param>
        /// <param name="PageSize">页面大小,如:10</param>
        /// <param name="PageIndex">页面索引从1开始,如:1</param>
        /// <returns>返回示例:select * from (select *,ROW_NUMBER() OVER(order by id) as RNO__ from (select id,name from person where age>10) as inner__ ) outer__ WHERE outer__.RNO__ BETWEEN (0*10+1) AND (1*10)</returns>
        public override string GetSqlForPageSize(string selectSql, string strOrder, int PageSize, int PageIndex)
        {
            string sql = "";
            if (this.IsSqlServerVersion2012Compatible())
            {
                //使用fetch分页
                sql = $"{selectSql} {strOrder} offset {(PageIndex - 1) * PageSize} rows fetch next {PageSize} rows only";
            }
            else
            {
                //使用ROW_NUMBER分页
                sql = $"select * from (select *,ROW_NUMBER() OVER({strOrder}) as RNO__ from ({selectSql}) as inner__ ) outer__ WHERE outer__.RNO__ BETWEEN ({PageIndex - 1}*{PageSize}+1) AND ({PageIndex}*{PageSize})";
            }
            return sql;
        }
        #endregion

        #region Is系列: 判断表/视图/列/存储过程是否存在
        private string IsProcedureExistSql(string procName)
        {
            procName = DealSqlInject(procName);
            var objName = ParseObjectName(procName);
            string sql = $"select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME='{objName.Name}' and ROUTINE_TYPE='PROCEDURE'";
            if (objName.SegCount >= 2) sql += $" and ROUTINE_SCHEMA='{objName.SchemaName}'";
            else sql += $" and ROUTINE_SCHEMA={GetCurrentSchemaSqlSeg()}";
            if (objName.SegCount >= 3) sql += $" and ROUTINE_CATALOG='{objName.DataBaseName}'";
            else sql += $" and ROUTINE_CATALOG={GetCurrentDataBaseSqlSeg()}";
            return sql;
        }

        public override bool IsProcedureExist(string procName)
        {
            var sql = IsProcedureExistSql(procName);
            int r = SelectScalar<int>(sql);
            return r > 0;
        }

        private string IsTriggerExistSql(string triggerName)
        {
            triggerName = DealSqlInject(triggerName);
            var objName = ParseObjectName(triggerName);

            string sql = $"select * from sys.objects where type='TR' and name='tri_test_after_update'";
            if (objName.SegCount >= 2) sql += $" and schema_id='{objName.SchemaName}'";
            else sql += $" and schema_id={GetCurrentSchemaSqlSeg()}";
            if (objName.SegCount >= 3) sql += $" and parent_object_id='{objName.DataBaseName}'";
            else sql += $" and parent_object_id={GetCurrentDataBaseSqlSeg()}";
            return sql;
        }
        public override bool IsTriggerExist(string triggerName)
        {
            var sql = IsTriggerExistSql(triggerName);
            int r = SelectScalar<int>(sql);
            return r > 0;
        }
        #endregion

        #region 基于数据库的Id和流水号生成器       
        /// <summary>
        /// 初始化基础数据库缓存的Id和流水号生成环境
        /// </summary>
        /// <returns></returns>
        protected override bool InitDBCacheGenerator()
        {
            if (!IsTableExist(Settings.DBCacheGeneratorLogTableName))
            {
                #region 新建表 Settings.DBCacheGeneratorLogTableName
                ExecuteSql($@"
create table {Settings.DBCacheGeneratorLogTableName}(
  id int identity(1,1),--错误序号
  err_proc  varchar(200),--出现错误的存储过程或 触发器的名称
  lock_str  varchar(200),--申请的锁资源锁
  err_num   int,--错误号
  err_severity  int,--严重性
  err_state int,--错误状态号  
  err_line  int,--导致错误的例程中的行号
  err_msg   varchar(200),--错误消息的完整文本
  err_time  datetime --错误发生时间
)");
                #endregion
            }
            if (!IsTableExist(Settings.DBCacheGeneratorIdTableName))
            {
                #region 新建表 Settings.DBCacheGeneratorIdTableName
                ExecuteSql($@"
create table {Settings.DBCacheGeneratorIdTableName}(
	 tablename varchar(50), -- 引用表名
	 colname varchar(50), -- 引用列名
	 currentid bigint,
	 primary key(tablename,colname)
)");
                #endregion
            }
            if (!IsProcedureExist(Settings.DBCacheGeneratorIdProcedureName))
            {
                var procName = Settings.DBCacheGeneratorIdProcedureName;
                var tableName = Settings.DBCacheGeneratorIdTableName;
                var errTableName = Settings.DBCacheGeneratorLogTableName;
                #region 新建存储过程 Settings.DBCacheGeneratorIdProcedureName
                ExecuteSql($@"
-- Version=2021-09-19
/*
正常情况:
	1. 无缓存,实际表里也没匹配到
	2. 无缓存,实际表里匹配到
	3. 有缓存
异常情况:
	1. 实际表不存在等,造成查询报错
	2. 获取锁超时
*/
/* 调用示例
exec {procName}      
	@tablename='test',      
	@colname='id', 
	@count=1
*/
create proc {procName}
@tablename varchar(200),
@colname varchar(200),
@count int=1
as
begin tran
    declare @current bigint,@sql nvarchar(1000),@lockstr nvarchar(200), @result int;
	declare @err_message varchar(500),@err_serverity varchar(50),@err_state varchar(50);
    begin
	    -- 申请锁,20秒
	    set @lockstr='dbutil:newid:'+@tablename+'_'+@colname;
	    EXEC @result =sp_getAppLock @resource=@lockstr,@lockMode='Exclusive',@lockOwner='Transaction',@lockTimeout='20000';
	    if(@result >= 0)
	    begin
		    begin try
			    --先从id表里查找
			    set @current = (select top 1 currentid from {tableName} where tablename = @tablename and colname = @colname)
			    if @current is null
			      --表里尚未缓存,从实际表里查找id列的最大值
			      begin
				    set @sql='select @current=max('+@colname+') from '+@tablename	
				    exec sp_executesql @sql,N'@current bigint output',@current output
				    if(@current is null)
					    --实际表里也没有值
					    begin
                            set @current=@count
						    insert into {tableName}(tablename, colname, currentid) values(@tablename, @colname, @current)
						    select @current
					    end
				    else
					    --实际表里有值
					    begin
						    set @current+=@count
						    insert into {tableName}(tablename, colname, currentid) values(@tablename, @colname, @current)
						    select @current
					    end    
			      end
			    else
				    --表里已经缓存
				    begin
					    set @current += @count
					    update {tableName} set currentid = @current where tablename = @tablename and colname = @colname
					    select @current
			       end
			    exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
                commit
                return
		    end try
		    begin catch
			    exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
				select @err_message=ERROR_MESSAGE(),@err_serverity=ERROR_SEVERITY(),@err_state=ERROR_STATE();
				RAISERROR (@err_message, @err_serverity,@err_state );
                rollback;
			    INSERT INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
				    values('{procName}',@lockstr,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(), ERROR_LINE() ,ERROR_MESSAGE(),GetDate());                 
                return;
		    end catch
	    end
	    else
	    begin
            rollback;
		    INSERT INTO {errTableName}(err_proc,lock_str,err_msg,err_time) 
				    values('{procName}',@lockstr,'20秒内未获取到锁,申请锁返回:'+Convert(varchar,@result),GetDate());
			select @err_message='20秒内未获取到锁:'+@lockstr,@err_serverity=10,@err_state=1;
			RAISERROR (@err_message, 16,1 );
            return;
	    end
    end");
                #endregion
            }
            if (!IsTableExist(Settings.DBCacheGeneratorSNOTableName))
            {
                #region 新建表(流水号生成) Settings.DBCacheGeneratorSNOTableName
                ExecuteSql($@"
create table {Settings.DBCacheGeneratorSNOTableName}
(
	tablename varchar(50), -- 引用表名
	colname varchar(50),  -- 引用列名
	statictext varchar(200),
	machineidstr varchar(50),
	nowstr varchar(100),
	currentno bigint,
	primary key(tablename,colname,statictext)
)");
                #endregion
            }
            if (!IsProcedureExist(Settings.DBCacheGeneratorSNOProcedureName))
            {
                var procName = Settings.DBCacheGeneratorSNOProcedureName;
                var tableName = Settings.DBCacheGeneratorSNOTableName;
                var errTableName = Settings.DBCacheGeneratorLogTableName;
                #region 新建存储过程(流水号生成) Settings.DBCacheGeneratorSNOProcedureName
                ExecuteSql($@"
-- Version=2021-09-19
/*
正常情况:
	1. 无缓存,实际表里也没匹配到
	2. 无缓存,实际表里匹配到
	3. 有缓存,但时间戳对不上
	4. 有缓存,时间戳也对的上
异常情况:
	1. 实际表不存在等,造成查询报错
	2. 获取锁超时
*/

/* 调用实例
exec {procName}      
	@tablename='test',      
	@colname='sno',      
	@statictext='SNO',      
	@machineidstr=0000,      
	@nowstr='2021-09-19 00:00:00',       
	@likestr='SNO20210919%',      
	@startindex=11,      
	@count=1
*/

-- 其实, 除了自增的序列号其他部分已经在程序中计算完成,这个要根据程序计算的结果求出对应的序列号
create proc {procName}
@tablename varchar(50),-- 实际表名(关键值)
@colname varchar(50),-- 实际列名(关键值)
@statictext varchar(200),-- 静态文本,即: 除了时间戳和序列号的其他部分(包含机器id标识) (关键值)
@machineidstr varchar(50),-- 机器id标识,仅用作记录
@nowstr varchar(200),-- 当前流水号对应的时间戳字符串(格式为: yyyy-MM-dd HH:mm:ss)
@likestr varchar(200),-- 程序中计算的除了自增序列号的其他部分,如: 'SNO20210919%', 用于无缓存时从真实表中匹配(关键值)
@startindex int, -- 用于从实际表中查出流水号中截取序列号部分, 注意:程序传入的startindex是以0起始,而数据库中是以1起始(关键值) 
@count int=1 --批量生成时的数量
as
declare 
	@current bigint,
	@tempno nvarchar(200),
	@sql nvarchar(1000),
	@templen int,
	@lockstr nvarchar(200),
	@result int,
	@currentnowstr varchar(200);
declare @err_message varchar(500),@err_serverity varchar(50),@err_state varchar(50);

begin tran
-- 申请锁,20秒
set @lockstr='dbutil:newsno:'+@tablename+'_'+@colname+'_'+@statictext;
EXEC @result =sp_getAppLock @resource=@lockstr,@lockMode='Exclusive',@lockOwner='Transaction',@lockTimeout='20000';
if(@result >= 0)
	begin
		-- 获取到了锁
		begin try
			--先从流水号表里查找
			select top 1 @current=currentno,@currentnowstr=nowstr from {tableName} where tablename = @tablename and colname = @colname and statictext=@statictext
			if @current is null
			    --表里尚未缓存,从实际表里查找流水号列的最大值
			    begin
			    set @sql='select top 1 @tempno='+@colname+' from '+@tablename +' where '+@colname+' like '''+@likestr+''' order by '+@colname +' desc'
			    exec sp_executesql @sql,N'@tempno nvarchar(200) output',@tempno output
			    if(@tempno is null)
				    -- 实际表中也找不到参照
				    begin
					    insert into {tableName}(tablename, colname,statictext,machineidstr,nowstr, currentno) values(@tablename, @colname,@statictext,@machineidstr,@nowstr, @count)
					    select @count
				    end
			    else
				    -- 实际表中找到参照
				    begin
					    set @current=CONVERT(int,SUBSTRING(@tempno,@startindex+1,100))
					    set @current+=@count
					    insert into {tableName}(tablename, colname,statictext,machineidstr,nowstr, currentno) values(@tablename, @colname,@statictext,@machineidstr,@nowstr, @current)
					    select @current
				    end    
		        end
			    else
				    -- 缓存表里有值
				    begin
				    -- 判断时间戳是否匹配
				    if @currentnowstr=@nowstr
					    begin
						    -- 时间戳匹配
						    set @current += @count
						    update {tableName} set currentno = @current where tablename = @tablename and colname = @colname and statictext=@statictext
						    select @current					
					    end
				    else
					    begin
						    -- 时间戳不匹配,从新开始
						    set @current=@count
						    update {tableName} set currentno = @current,nowstr=@nowstr where tablename = @tablename and colname = @colname and statictext=@statictext
						    select @current	
					    end				
			    end
			    exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
			    commit;
			    return;
		end try
		begin catch
			exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
			select @err_message=ERROR_MESSAGE(),@err_serverity=ERROR_SEVERITY(),@err_state=ERROR_STATE();
			RAISERROR (@err_message, @err_serverity,@err_state );
            rollback;
			INSERT INTO {errTableName}(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time) 
				values('{procName}',@lockstr,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(), ERROR_LINE() ,ERROR_MESSAGE(),GetDate());			
			return;
		end catch
	end
else
	begin
		-- 未获取到锁
		rollback;
		INSERT INTO {errTableName}(err_proc,lock_str,err_msg,err_time) 
				values('{procName}',@lockstr,'未获取到锁,申请锁返回:'+Convert(varchar,@result),GetDate());
		select @err_message='20秒内未获取到锁:'+@lockstr,@err_serverity=10,@err_state=1;
		RAISERROR (@err_message, 16,1 );
		return;
	end");
                #endregion
            }
            return true;
        }

        /// <summary>
        /// 根据指定的表名和列名生成Id
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <returns></returns>
        protected override long InternalNewId(string tableName, string colName)
        {
            Ensure.NotNullOrEmptyOrWhiteSpace(tableName);
            Ensure.NotNullOrEmptyOrWhiteSpace(colName);
            EnsureInitGenerator();
            var id = SelectScalar<long>($"exec {Settings.DBCacheGeneratorIdProcedureName} '{DealSqlInject(tableName)}','{DealSqlInject(colName)}'");
            return id;
        }

        /// <summary>
        /// 根据指定的表名和列名批量生成Id
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <returns></returns>
        protected override List<long> InternalNewIds(string tableName, string colName, int count)
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            Ensure.NotNullOrEmptyOrWhiteSpace(tableName);
            Ensure.NotNullOrEmptyOrWhiteSpace(colName);
            EnsureInitGenerator();
            var id = SelectScalar<long>($"exec {Settings.DBCacheGeneratorIdProcedureName} '{DealSqlInject(tableName)}','{DealSqlInject(colName)}',{count}");
            var res = new List<long>();
            for (long i = id - count + 1; i <= id; i++)
            {
                res.Add(i);
            }
            return res;
        }

        /// <summary>
        /// 根据表名和列名生成流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <returns></returns>
        protected override string InternalNewSNO(string tableName, string colName, SerialFormat format)
        {
            Ensure.NotNullOrEmptyOrWhiteSpace(tableName);
            Ensure.NotNullOrEmptyOrWhiteSpace(colName);
            SerialFormat.ValidFormat(format);
            EnsureInitGenerator();
            var now = DateTime.Now;
            var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(format, now, DotNetCommon.Machine.MachineIdString);
            var sql = $@" 
exec {Settings.DBCacheGeneratorSNOProcedureName} 
	@tablename='{DealSqlInject(tableName)}',
	@colname='{DealSqlInject(colName)}',
	@statictext='{statictext}',
	@machineidstr={machineIdString},
	@nowstr='{snoNow.ToString(format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}', 
	@likestr='{likestr}',
	@startindex={startindex},
	@count=1";
            long no = SelectScalar<long>(sql);
            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var sno = likestr.Substring(0, startindex);
            var s = no.ToString();
            if (s.Length > chunk.Length)
            {
                sno += s;
            }
            else
            {
                sno += s.PadLeft(chunk.Length, '0');
            }
            return sno;
        }

        /// <summary>
        /// 根据表名和列名批量生成流水号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <returns></returns>
        protected override List<string> InternalNewSNOs(string tableName, string colName, SerialFormat format, int count)
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            Ensure.NotNullOrEmptyOrWhiteSpace(tableName);
            Ensure.NotNullOrEmptyOrWhiteSpace(colName);
            SerialFormat.ValidFormat(format);
            EnsureInitGenerator();
            var now = DateTime.Now;
            var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(format, now, DotNetCommon.Machine.MachineIdString);
            var sql = $@" 
exec {Settings.DBCacheGeneratorSNOProcedureName} 
	@tablename='{DealSqlInject(tableName)}',
	@colname='{DealSqlInject(colName)}',
	@statictext='{statictext}',
	@machineidstr={machineIdString},
	@nowstr='{snoNow.ToString(format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}', 
	@likestr='{likestr}',
	@startindex={startindex},
	@count={count}";
            long no = SelectScalar<long>(sql);
            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var res = new List<string>();
            for (long i = no - count + 1; i <= no; i++)
            {
                var sno = likestr.Substring(0, startindex);
                var s = i.ToString();
                if (s.Length > chunk.Length)
                {
                    sno += s;
                }
                else
                {
                    sno += s.PadLeft(chunk.Length, '0');
                }
                res.Add(sno);
            }
            return res;
        }
        #endregion

        #region 数据库管理对象 Manage
        private DBManage _dbmanage = null;
        /// <summary>
        /// 数据库管理对象
        /// </summary>
        public override DBManage Manage => _dbmanage != null ? _dbmanage : _dbmanage = new SqlServerManage(this);
        #endregion

        #region 运算符
        protected override DateTimeSqlSegment GetDateTimeSqlSegment() => new SqlServerDateTimeSqlSegment(this);
        protected override StringSqlSegment GetStringSqlSegment() => new SqlServerStringSqlSegment(this);
        #endregion

        #region 使用SqlBulkCopy批量插入数据
        public override void BulkCopy(DataTable dt, string tableName = null, int timeoutSeconds = 60 * 30, int notifyAfter = 0, Func<long, bool> callBack = null)
        {
            if (string.IsNullOrWhiteSpace(tableName)) tableName = dt.TableName;
            if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("必须指定要目的表名!");
            SqlBulkCopy sbc = null;
            if (IsTransaction) sbc = new SqlBulkCopy((SqlConnection)CurrentConnection, SqlBulkCopyOptions.Default, (SqlTransaction)CurrentTransaction);
            else if (IsSession) sbc = new SqlBulkCopy((SqlConnection)CurrentConnection);
            else sbc = new SqlBulkCopy((SqlConnection)GetNewConnection());

            sbc.BulkCopyTimeout = timeoutSeconds;
            sbc.DestinationTableName = tableName;
            sbc.NotifyAfter = notifyAfter;
            if (callBack != null) sbc.SqlRowsCopied += (object sender, SqlRowsCopiedEventArgs e) =>
            {
                e.Abort = callBack(e.RowsCopied);
            };

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
            }
            sbc.WriteToServer(dt);
        }
        #endregion

        #region 获取刚插入的自增id的Sql语句
        /// <summary>
        /// 获取刚插入的自增id的Sql语句
        /// </summary>
        /// <returns></returns>
        public override string GetLastInsertedId()
        {
            return "select SCOPE_IDENTITY();";
        }
        /// <summary>
        /// 获取刚插入的自增id的Sql语句
        /// </summary>
        /// <returns></returns>
        public override string LastInsertedId()
        {
            return "SCOPE_IDENTITY();";
        }
        #endregion

        #region 基于数据库的分布式锁 RunInLock
        private static ConcurrentDictionary<string, SemaphoreSlim> _lockSemaphores = new ConcurrentDictionary<string, SemaphoreSlim>();
        protected override async Task<T> RunInLockInternalAsync<T>(string lock_str, Func<Task<T>> func, int getLockTimeoutSecond)
        {
            //先单机拦截并发
            var now = DateTime.Now;
            var asyncLock = _lockSemaphores.GetOrAdd(lock_str, _ => new SemaphoreSlim(1, 1));
            var b = await asyncLock.WaitAsync(getLockTimeoutSecond * 1000);
            if (!b) throw new Exception($"获取锁[{lock_str}]超时,单机内超时.");
            try
            {
                //单机通过后,再数据库锁内拦截
                var now2 = DateTime.Now;
                var timeout2 = getLockTimeoutSecond - (int)(now2 - now).TotalSeconds;
                if (timeout2 < 0) timeout2 = 0;
                lock_str = "dbutil:runinlock:" + lock_str;
                lock_str = DealSqlInject(lock_str);
                var sql = $@"declare @result int
exec @result=sp_getAppLock @resource='{lock_str}',@lockMode='Exclusive',@lockOwner='Session',@lockTimeout='{timeout2}'
select @result";
                var res = await SelectScalarAsync<int>(sql);
                if (res >= 0)
                {
                    //获取到锁
                    T result = default;
                    try
                    {
                        result = await func();
                    }
                    finally
                    {
                        try
                        {
                            await ExecuteSqlAsync($@"declare @result int
exec @result=sp_releaseapplock @resource='{lock_str}',@lockOwner='Session';
select @result");
                        }
                        catch (Exception ex)
                        {
                            logger.LogError($"尝试释放基于数据库的分布式锁失败(lock_str={lock_str}),异常信息:{ex?.Message}");
                        }
                    }
                    return result;
                }
                else
                {
                    throw new Exception($"数据库获取锁失败: sp_getAppLock 返回:{res}");
                }
            }
            finally
            {
                try { asyncLock.Release(); } catch { }
            }
        }
        #endregion

        #region 序列
        /// <summary>
        /// 是否支持序列, sqlsever 2012版本引入的序列
        /// </summary>
        /// <returns></returns>
        public override bool IsSupportSequence() => this.IsSqlServerVersion2012Compatible();

        /// <summary>
        /// 获取 下一个序列值 的sql语句
        /// </summary>
        /// <param name="name">序列名称</param>
        /// <returns></returns>
        public override string NextSequenceValue(string name) => $"next value for {name}";
        #endregion
    }
}
